package cn.fldong.employee.dao;

import java.sql.*;

// 数据库工具类
public class DBUtil {
    // 1.定义表示数据库URL的字符串，访问的是本地数据库，数据库名是TestDB
    private String url = "jdbc:mysql://localhost:3306/EmployeeDB?useUnicode=true&characterEncoding=utf-8";
    private String user = "root";
    private String password = "123456";


    // 2.定义数据库操作各种对象
    private  Connection conn = null;
    private  PreparedStatement ps = null;
    private  ResultSet rs = null;

    // 3.定义数据库操作方法,获取数据库连接对象
    private  void getConnection() {
        try {
            if (conn == null) {
                // 加载驱动程序
                Class.forName("com.mysql.cj.jdbc.Driver");
                // 获取数据库连接对象
                conn = DriverManager.getConnection(url, user, password);
            }
        } catch (ClassNotFoundException e) {
            System.out.println("1-1.database driver not found！");
            e.printStackTrace();
        }catch (SQLException e) {
            System.out.println("1-2.database connection failed！");
            e.printStackTrace();
        }
    }

    // 4-1.定义数据库操作方法,执行查询语句
    public  ResultSet executeQuery(String sql) {
        return executeQuery(sql,null);
    }
    // 4-2.定义数据库操作方法,执行带参数的查询语句
    public ResultSet executeQuery(String sql,String[] params) {
        // 获取数据库连接对象
        getConnection();
        try {
            // 创建PreparedStatement对象
            ps = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);

            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    ps.setString(i+1, params[i]);
                }
            }

            // 执行查询语句，并返回ResultSet对象
            rs = ps.executeQuery();
        } catch (Exception e) {
            System.out.println("2-1.execute query failed！");
            e.printStackTrace();
        }
        return rs;
    }

    // 5-1.定义数据库操作方法,执行更新语句
    public  int executeUpdate(String sql) {
        return executeUpdate(sql,null);
    }
    // 5-2.定义数据库操作方法,执行带参数的更新语句
    public int executeUpdate(String sql,String[] params) {
        int count = 0;
        getConnection();
        try {
            // 创建PreparedStatement对象
            ps = conn.prepareStatement(sql);

            if (params != null && params.length > 0) {
                for (int i = 0; i < params.length; i++) {
                    ps.setString(i+1, params[i]);
                }
            }
            // 执行更新语句，并返回受影响的行数
            count = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println("2-2.execute update failed！");
            e.printStackTrace();
        }
        // 关闭数据库连接对象
        closeConnection();
        return count;
    }

    // 6.定义数据库操作方法,关闭数据库连接对象
    public  void closeConnection() {
        if (rs != null) {
            try {
                rs.close();
            } catch (SQLException e) {
                System.out.println("4-1.close ResultSet failed！");
            }
        }
        if (ps != null) {
            try {
                ps.close();
            } catch (SQLException e) {
                System.out.println("4-2.close PreparedStatement failed！");
            }
        }
        if (conn != null) {
            try {
                conn.close();
            }catch (SQLException e){
                System.out.println("4-3.close Connection failed！");
            }
        }
    }
}
